package com.ww.idiom.db;

import java.io.File;
import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.ww.core.util.Logger;
import com.ww.idiom.entity.Idiom;

public class IdiomDbHelper extends SQLiteOpenHelper {

	private SQLiteDatabase sqlitedb;
	private static final String T_IDIOM = "T_IDIOM",T_STORE="T_STORE";
	private static final String NAME_WORD = "NAME_WORD", BAIKE = "BAIKE",
			SYNAND = "SYNAND", EXPLAIN = "EXPLAIN",EXAMPLE="EXAMPLE";

	/**
	 * @param context Context
	 */
	public IdiomDbHelper(Context context) {
		super(context, Constants.DATABASE_PATH + Constants.DATABASE_NAME, null, 1);
		File dir = new File(Constants.DATABASE_PATH);
		if (!dir.exists()) {
			dir.mkdir();
		}

		try{
		SQLiteDatabase.openOrCreateDatabase(Constants.DATABASE_PATH + Constants.DATABASE_NAME, null);
		}catch(Exception e){
			e.printStackTrace();
		}
		sqlitedb = getWritableDatabase();
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(createWordTable());
		db.execSQL(createStoreTable());

	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}

	/**
	 * 鍒涘缓姹夊瓧琛?
	 * @return  sql
	 */
	private String createWordTable() {
		StringBuffer sql1 = new StringBuffer("CREATE TABLE  IF NOT EXISTS " + T_IDIOM
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT ");
		sql1.append("," + NAME_WORD + " text");
		sql1.append("," + EXPLAIN + " text");
		sql1.append("," + BAIKE + " text");
		sql1.append("," + SYNAND + " text");
		sql1.append("," + EXAMPLE + " text");
		sql1.append(" )");
		return sql1.toString();
	}

	/**
	 * 鍒濆鍖栨眽瀛椾俊鎭?涓嶅寘鎷鎯?.
	 * @param words 姹夊瓧鍒楄〃
	 */
	public void saveWord(List<String> words) {
		for (int i = 0; i < words.size(); i++) {
			try {
				ContentValues cvD = new ContentValues();
				cvD.put(NAME_WORD, words.get(i));
				if (checkExsit(T_IDIOM, words.get(i))) {
					sqlitedb.update(T_IDIOM, cvD, NAME_WORD + "=?", new String[] {words.get(i)});
					Logger.info("saveWord update:"+words.get(i));
				} else {
					sqlitedb.insert(T_IDIOM, null, cvD);
					Logger.info("saveWord insert:"+words.get(i));
				}
			} catch (Exception e) {
				e.printStackTrace();
			}
		}

		Logger.info("saveWord:" + words.size());
		sqlitedb.close();
	}

	/**
	 * 鏇存柊姹夊瓧璇︽儏淇℃伅.
	 * @param word Word
	 */
	public void updateWord(Idiom word) {
		try {
			ContentValues cvD = new ContentValues();
			cvD.put(EXPLAIN, word.getDetailmean());
			cvD.put(BAIKE, word.getBaike());
			cvD.put(SYNAND, word.getSynAnt());
			cvD.put(EXAMPLE, word.getExample());
			
//			if (checkExsit(T_IDIOM, word.getName())) {
				if (!sqlitedb.isOpen()) {
					sqlitedb = getWritableDatabase();
				}
				sqlitedb.update(T_IDIOM, cvD, NAME_WORD + "=?", new String[] { word.getName() });

				Logger.info("updateWord :"+word.getName());
//			}

		} catch (Exception e) {
			e.printStackTrace();
		}
		sqlitedb.close();
	}
	
	/**
	 * 创建收藏表
	 * @return  sql
	 */
	private String createStoreTable() {
		StringBuffer sql1 = new StringBuffer("CREATE TABLE  IF NOT EXISTS " + T_STORE
				+ "(id INTEGER PRIMARY KEY AUTOINCREMENT ");
		sql1.append("," + NAME_WORD + " text");
		sql1.append(" )");
		return sql1.toString();
	}
	
	/**
	 * 更新收藏(有则删除，无则添加).
	 * @param idWord 汉字id
	 * @param String 返回信息
	 */
	public String updateStore(String nameWord) {
		String str = null;
		try {
			ContentValues cvD = new ContentValues();
			if (checkExsit(T_STORE, nameWord)) {
				str = "取消收藏";
				sqlitedb.delete(T_STORE, NAME_WORD + "=?", new String[] { nameWord + "" });
			} else {
				str = "收藏";
				cvD.put(NAME_WORD, nameWord);
				sqlitedb.insert(T_STORE, null, cvD);
			}
			str += "成功";
		} catch (Exception e) {
			e.printStackTrace();
			str += "失败";
		} finally {
			sqlitedb.close();
		}
		return str;
	}
	
	/**
	 * 获取收藏列表.
	 * @return List<Word>
	 */
	public ArrayList<String> getStoreList() {
		ArrayList<String> list = new ArrayList<String>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM  " + T_STORE;

			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					list.add(cur.getString(1));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return list;
	}

	/**
	 * 鍒犻櫎瀛︿範淇℃伅.
	 */
	public void deleteAll() {
		try {
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			sqlitedb.delete(T_IDIOM, null, null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlitedb.close();
		}
	}
	
	/**
	 * 鍒犻櫎瀛︿範淇℃伅.
	 */
	public void geBack() {
		try {
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			sqlitedb.delete(T_STORE, null, null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlitedb.close();
		}
	}
	
	
	/**
	 * 鍒犻櫎瀛︿範淇℃伅.
	 */
	public void delete(String nameWord) {
		try {
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			sqlitedb.delete(T_IDIOM, NAME_WORD + "=?", new String[] { nameWord + "" });
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlitedb.close();
		}
	}


	
	/**
	 * 鍒犻櫎瀛︿範淇℃伅.
	 */
	public void deleteEmpty() {
		try {
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			sqlitedb.delete(T_IDIOM, EXPLAIN + " is null",null);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			sqlitedb.close();
		}
	}
	
	/**
	 * 鍒ゆ柇姹夊瓧琛ㄦ槸鍚︿负绌?	
	 * @return  boolean (true:绌?false:涓嶇┖)
	 */
	public boolean checkEmpty() {
		boolean bool = false;
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + T_IDIOM, null);
		if (cur != null) {
			bool = cur.getCount() == 0;
		}
		if (cur != null) {
			cur.close();
		}
		sqlitedb.close();
		return bool;
	}

	/**
	 * 鏌ヨ鏄惁瀛樺湪.	
	 * @param table 琛?
	 * @param id 鍏抽敭瀛?
	 * @return  boolean  (true:瀛樺湪 false:涓嶅瓨鍦?
	 */
	private boolean checkExsit(String table, String id) {
		boolean bool = false;
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + table + " WHERE " + NAME_WORD + "='" + id + "'", null);
		if (cur != null) {
			bool = cur.getCount() > 0;
		}
		if (cur != null) {
			cur.close();
		}
		Logger.info("checkExsit:"+id+"  "+bool);
		return bool;
	}
	
	/**
	 * 判断汉字表是否为空.	
	 * @return  boolean (true:空 false:不空)
	 */
	public boolean checkStore(String id) {
		return checkExsit(T_STORE, id);
	}
	

	/**
	 * 鑾峰彇姹夊瓧涓暟.
	 * @return int
	 */
	public int getWordCount() {
		int count = 0;
		Cursor cur = sqlitedb.rawQuery("SELECT * FROM " + T_IDIOM+" WHERE "+EXPLAIN+" is  NOT null", null);
		if (cur != null) {
			count = cur.getCount();
			cur.close();
		}
		return count;
	}

	/**
	 * 获取汉字列表.
	 * @return List<Word>
	 */
	public ArrayList<String> getWordList() {
		ArrayList<String> list = new ArrayList<String>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_IDIOM +" WHERE "+EXPLAIN+" is  NOT null";
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}

			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					list.add(cur.getString(1));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return list;
	}
	
	/**
	 * 获取汉字列表.
	 * @return List<Word>
	 */
	public ArrayList<String> getEmptyWordList() {
		ArrayList<String> list = new ArrayList<String>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_IDIOM +" WHERE "+EXPLAIN+" is null";
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}

			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					list.add(cur.getString(1));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return list;
	}
	
	/**
	 * 获取汉字列表.
	 * @return List<Word>
	 */
	public ArrayList<String> query(String like) {
		ArrayList<String> list = new ArrayList<String>();
		Cursor cur = null;
		try {
			String sql = "SELECT * FROM " + T_IDIOM +" WHERE "+NAME_WORD+" like '%"+like+"%' "+" AND "+EXPLAIN+" is NOT null";;
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}

			cur = sqlitedb.rawQuery(sql, null);
			if (cur != null) {
				while (cur.moveToNext()) {
					list.add(cur.getString(1));
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return list;
	}
	
	/**
	 * 鑾峰彇姹夊瓧璇︽儏.
	 * @param idWord 姹夊瓧id
	 * @return Word
	 */
	public Idiom getWord(String idWord) {
		Idiom d = null;
		Cursor cur = null;
		try {
			Logger.info("NAME_WORD="+idWord);
			String sql = "SELECT * FROM " + T_IDIOM + " WHERE " + NAME_WORD + "=? "+" AND "+EXPLAIN+" is NOT null";;
			if (!sqlitedb.isOpen()) {
				sqlitedb = getWritableDatabase();
			}
			cur = sqlitedb.rawQuery(sql, new String[] { idWord + "" });
			if (cur != null) {
				while (cur.moveToNext()) {
					d = makeWord(cur);
				}
			}

		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (cur != null) {
				cur.close();
			}
			sqlitedb.close();
		}
		return d;
	}
	private Idiom makeWord(Cursor cur) {
		Idiom d = new Idiom();
		d.setName(cur.getString(1));
		d.setDetailmean(cur.getString(2));
		d.setBaike(cur.getString(3));
		d.setSynAnt(cur.getString(4));
		d.setStore(checkStore(cur.getString(1)));
		return d;
	}

}
